Imports Microsoft.VisualBasic
Imports Oracle.DataAccess.Client  'Namespace For Oracle Database
Imports System.Data
'---------------------------------------------------------------------
'CLASSNAME : Connect 
'DESCRIPTION : USE FOR CONNECT ORACLE DATABASE
'CREATEDATE : 10/12/2551
'CREATEBY : PATTARAWUT CHANNGAM
'---------------------------------------------------------------------
Public Class Connect
    Public Conn As OracleConnection             'Connect Oracle Database
    Private ConStr As String                    'Connect String
    Private Message As String                   'Return Massage
    Private NumRows As Integer                  'NumRows
    Private DT As New DataTable
    Private DS As New DataSet
    Private OrclDA As New OracleDataAdapter
    Private OrclCM As New OracleCommand
    '-------------Contructor-----------------
    Public Sub New()
        Me.OracleConnect("orcl_1.3.5.101", "exatpty", "exatpty")
    End Sub
    Private Function OracleConnect(ByVal DataSource As String, ByVal UserName As String, ByVal PassWord As String)
        'Me.ConStr = "Data Source=" & DataSource & " ;Persist Security Info=True;User ID=" & UserName & ";Password=" & PassWord & "; Min Pool Size=50; Max Pool Size=50;"
        Me.ConStr = "Data Source=" & DataSource & " ;Persist Security Info=True;User ID=" & UserName & ";Password=" & PassWord & "; "

        Try
            Me.Conn = New OracleConnection(Me.ConStr)
            Try
                'Conn.Open()
                Me.Message = "Connected"
            Catch ex As Exception
                Me.Message = "Disconnected"
            End Try
        Catch ex As Exception
            Me.SetMessage(ex.Message)
        End Try
        'Conn.Close()
        Return Me.Conn
    End Function
    Private Function SetMessage(ByVal Message As String)
        Return Me.Message = Message
    End Function
    Function GeMessage()
        Return Me.Message
    End Function

    Private Sub Result(ByVal SQL As String)
        DT.Clear()
        DS.Clear()
        Me.Conn.Open()
        OrclCM.Connection = Me.Conn
        OrclCM.CommandType = CommandType.Text
        OrclCM.CommandText = SQL
        OrclDA.SelectCommand = OrclCM
        Try
            OrclDA.Fill(DS)
            DT = DS.Tables(0)
        Catch ex As Exception
            Me.SetMessage(ex.Message)
        End Try
        Me.OrclCM.Dispose()
        Me.Conn.Close()

    End Sub
    Public Function GetData(ByVal SQL As String)
        Result(SQL)
        Return Me.DT
    End Function
    Public Function GetData(ByVal SQL As String, ByVal Columns As String, ByVal Rows As Integer)
        Result(SQL)
        Me.NumRows = DT.Rows.Count
        If DT.Rows.Count <> 0 Then
            If DS.Tables(0).Rows(Rows)(Columns).ToString <> Nothing Then
                Return DS.Tables(0).Rows(Rows)(Columns)
            End If
        Else
            Return Nothing
        End If
    End Function

    Public Function GetNumRows()
        Return Me.NumRows
    End Function

    Public Sub Query(ByVal SQL As String)
        Try
            Me.Conn.Open()
            OrclCM.Connection = Me.Conn
            OrclCM.CommandType = CommandType.Text
            OrclCM.CommandText = SQL
            OrclCM.ExecuteNonQuery()
            Me.Conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToCharArray, MsgBoxStyle.Critical, "Error")
            Me.SetMessage(ex.Message)
        End Try
        Me.OrclCM.Dispose()
        Me.Conn.Close()
    End Sub
End Class
